﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 30.03.2011
-- Description:	Отчет Долги производства
-- =============================================
CREATE PROCEDURE [dbo].[report_manufacture_debts] 
	@p_nsi_type_of_activity_id int,
	@p_nsi_organization_id int,
	@p_contract_id	int,
	@p_xml_products xml,
	@p_curreny_year bit
AS
BEGIN
	SET NOCOUNT ON;
		
	declare @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml_products

	SELECT     c.ID AS CONTRACT_ID, pu.NAME AS PRODUCT_NAME, o.SHORT_NAME AS ORGANIZATION_NAME, c.NUMBER AS CONTRACT_NUMBER, 
                      ISNULL(SUM(pu.PAYMENT_IN_SUM_CUR),0) * 100 / SUM(pc.COST) AS PAYMEN_PERCENTAGE, 
                      dbo.f_subreport_paid_products(c.ID, @p_curreny_year, cp.NSI_CURRENCY_ID, NULL, pu.NAME) AS PAYMENT_DATE, ISNULL(SUM(pi.PAY_SUM), 0) AS PAID_RUR, 
                      dbo.f_convert_to_rur(SUM(pc.COST), GETDATE(), cp.NSI_CURRENCY_ID) - isNull(SUM(pu.PAYMENT_IN_SUM_RUB),0) 
                      AS REST_SUM_RUR, COUNT(*) AS PRODUCT_COUNT, pu.CP_STAGE_END_DATE as STAGE_END_DATE
FROM         VIEW_SEARCH_P_U_FOR_FORMS AS pu INNER JOIN
                      CP_STAGE AS cs ON cs.ID = pu.CP_STAGE_ID INNER JOIN
                      PID ON PID.ID = pu.PID INNER JOIN
                      CONTRACT AS c ON c.ID = PID.CONTR_ID INNER JOIN
                      NSI_ORGANIZATION AS o ON o.ID = c.NSI_ORGANIZATION_ID INNER JOIN
                      PRODUCT_COST AS pc ON pu.ID = pc.PRODUCT_UNIT_ID INNER JOIN
                      COST_PROTOCOL AS cp ON cp.ID = pc.COST_PROTOCOL_ID LEFT OUTER JOIN
                          (SELECT     pit.PRODUCT_UNIT_ID AS PU_ID, ISNULL(SUM(pit.SUM_RUB),0) AS PAY_SUM
                            FROM          PAYMENT_IN AS p INNER JOIN
                                                   PAYMENT_IN_ITEM AS pit ON p.ID = pit.PAYMENT_IN_ID
                            WHERE      (@p_curreny_year = 0) OR
                                                   (YEAR(p.DATE) = YEAR(GETDATE()))
                            GROUP BY pit.PRODUCT_UNIT_ID) AS pi ON pi.PU_ID = pu.ID
    
    WHERE pu.NSI_PRODUCT_STATE_ID <= 2 AND pu.TYPE = 1 AND pu.PAYMENT_IN_SUM_CUR > 0
    AND
    (
		(@p_nsi_type_of_activity_id is null)
		OR
		(c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
    )
    AND
    (
		(@p_nsi_organization_id is null)
		OR
		(c.NSI_ORGANIZATION_ID = @p_nsi_organization_id)
    )
     AND
    (
		(@p_contract_id is null)
		OR
		(c.ID = @p_contract_id)
    )
    
    AND
		(cs.END_DATE < GETDATE())
    
    AND
		(
			(@p_xml_products is null)
			OR
			(
				pu.NSI_PRODUCT_ID IN (				
				SELECT id FROM  OPENXML(@docHandle, N'/products/product') WITH (id int)
				)
			)
		)
	
    GROUP BY pu.NAME, pu.CP_STAGE_END_DATE, o.SHORT_NAME, c.NUMBER, cp.NSI_CURRENCY_ID, c.ID
    
    EXEC sp_xml_removedocument @docHandle
END